import json
import pymysql

class sqlAnalysisUtils(object):

    def __init__(self):

        self.sql_config_path = "../global_configs/mysql_db_config.json"
        self.conn = self.connect_to_sql(self.sql_config_path)


    def load_config_file(self, json_file='../configs/test.json'):
        try:
            with open(json_file, 'r', encoding='utf-8') as file:
                data = json.load(file)
        except FileNotFoundError as e:
            print("配置文件 %s 未找到: %s", json_file, e)
            raise  # 抛出异常，停止后续执行
        except json.JSONDecodeError as e:
            print("解析配置文件 %s 时出错: %s", json_file, e)
            raise  # 抛出异常，停止后续执行
        except Exception as e:
            print("读取配置文件时发生未知错误: %s", e)
            raise  # 抛出异常，停止后续执行
        return data

    def connect_to_sql(self, config_path):
        db_config_path = self.load_config_file(json_file=config_path)
        # print("db", db_config_path['database']['host'])
        conn = pymysql.connect(
            host=db_config_path['database']['host'],
            user=db_config_path['database']['user'],
            passwd=db_config_path['database']['password'],
            port=db_config_path['database']['port'],
            db=db_config_path['database']['db'],  # 连接到数据库
            charset=db_config_path['database']['charset']
        )
        return conn

    def search_data_statics(self, org_name: str):
        metadata_db_name = f'{org_name}_metadata'
        book_attr_db_name = f'{org_name}_book_attr'
        all_user_db_name = f'{org_name}_all_user_data'
        all_item_db_name = f'{org_name}_all_item_data'
        all_item_sql = f"SELECT ITEM_ID FROM {all_item_db_name} ORDER BY ITEM_ID DESC LIMIT 1;"
        book_attr_sql = f"SELECT RECORD_ID FROM {book_attr_db_name} ORDER BY RECORD_ID DESC LIMIT 1;"
        metadata_sql = f"SELECT RECORD_ID, MD_ID FROM {metadata_db_name} ORDER BY RECORD_ID DESC LIMIT 1;"
        all_user_sql = f"SELECT USER_ID FROM {all_user_db_name} ORDER BY USER_ID DESC LIMIT 1;"
                # Execute the queries and fetch the results
        try:
            cursor = self.conn.cursor()
            
            # Execute each query and store the results
            cursor.execute(all_item_sql)
            all_item_result = cursor.fetchone()  # Get the result of the query
            
            cursor.execute(book_attr_sql)
            book_attr_result = cursor.fetchone()
            
            cursor.execute(metadata_sql)
            metadata_result = cursor.fetchone()
            
            cursor.execute(all_user_sql)
            all_user_result = cursor.fetchone()
            
            # Return results as a dictionary
            results = {
                "all_item_result": all_item_result,
                "book_attr_result": book_attr_result,
                "metadata_result": metadata_result,
                "all_user_result": all_user_result
            }
            cursor.close()
            return results
        
        except Exception as e:
            print(f"Error during query execution: {e}")
            return None

    
if __name__ == "__main__":
    sau = sqlAnalysisUtils()
    res = sau.search_data_statics("yzu")
    print(res)